1
The Enrolment Report Brief
DSAI2202Lab 6: Connecting Related Data

The registrar has student profiles, course details, and enrolments in separate tables. Build a report that puts the right student name beside the right course.

Your Three Requests

  1. Find Emma Li's active courses.
  2. List active enrolments with student names and course titles.
  3. Deliver the active PY101 roster for DSAI students.

Prepare PostgreSQL

  1. In pgAdmin, open the supplied lab6_7_setup.sql from the TA folder DSAI2202/lab6_7/ and run the complete file once.
  2. Confirm its final counts: 22 students, 8 courses, 37 enrolments.
  3. Run the connection setting on the right. Repeat it whenever you open a new database connection.
  4. Inspect the three tables before joining them.

Lab 7 uses the same setup, including assignments, submissions, and lms_roster. Today, use the three tables shown here. All people and records are fictional.

✍️ Trace Before You Join
Find enrolment E01. Which student ID and course code does it contain? Use the Data tab to recover the student's name and the course title.
Set schema
SET search_path TO lab6_7;
Students
SET search_path TO lab6_7;

SELECT student_id, student_name, programme
FROM students
ORDER BY student_id;
Courses
SET search_path TO lab6_7;

SELECT course_code, course_title, credits
FROM courses
ORDER BY course_code;
Enrolments
SET search_path TO lab6_7;

SELECT enrolment_id, student_id, course_code,
       enrolment_status, score
FROM enrolments
ORDER BY enrolment_id;